package com.kucun.controller;
import java.io.IOException;
import java.io.PrintWriter;
import java.lang.reflect.InvocationTargetException;
import java.net.URLDecoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONArray;

import org.apache.log4j.Logger;
import org.sdlc.com.database.BaseDaoInterface;
import org.sdlc.com.database.bean.CglibDynaBean;
import org.sdlc.com.database.impl.MysqlBaseDaoImpl;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import com.echarts.Test.util.EnhancedOption;
import com.echarts.axis.CategoryAxis;
import com.echarts.axis.SplitArea;
import com.echarts.axis.ValueAxis;
import com.echarts.code.Magic;
import com.echarts.code.MarkType;
import com.echarts.code.Tool;
import com.echarts.code.Trigger;
import com.echarts.data.PointData;
import com.echarts.feature.MagicType;
import com.echarts.json.GsonOption;
import com.echarts.series.Bar;
import com.echarts.series.Line;
import com.kucun.bean.Billdetail;
import com.kucun.bean.Goods;
import com.kucun.util.GsonUtils;


@Controller
@RequestMapping("/report1")
public class ReportController2 {
	private static final Logger logger = Logger.getLogger(ReportController2.class);
	/**
	 * 
	 * @param request
	 * @return
	 * @throws IOException 
	 * @throws InvocationTargetException 
	 * @throws IllegalAccessException 
	 */
	@RequestMapping("/reportalldate.html")
	public String getAllUser(HttpServletRequest request,HttpServletResponse response) throws IOException, IllegalAccessException, InvocationTargetException{
		String name=request.getParameter("name");
		 Map<String, Object> jsonMap = new HashMap<String, Object>();//定义map  
		 if(!"".equals(name)){
			 name = URLDecoder.decode(name, "utf-8");
	     } 
		BaseDaoInterface basedao=MysqlBaseDaoImpl.getInstance();
	        List<CglibDynaBean> lists=null;
	        String sql="SELECT s.goodname,SUM(s.kccount) AS kccount,SUM(s.rkd) AS rkd, SUM(s.ckd) AS ckd FROM("+
            " SELECT t.goodname,SUM(t.goodnumber) AS kccount,SUM(t.goodnumber) AS rkd,0 AS ckd FROM billdetail t WHERE t.billtype='入库单' AND t.states='已完成'  AND  t.clientname  like '%"+name+"%' GROUP BY t.goodid "+
            " UNION ALL "+
            " SELECT t.goodname,SUM(t.goodnumber)*-1 AS kccount,0 AS rkd, SUM(t.goodnumber) AS ckd FROM billdetail t WHERE t.billtype='出库单' AND t.states='已完成'  AND  t.clientname like '%"+name+"%' GROUP BY t.goodid"+ 
            " ) s GROUP BY s.goodname";
	        logger.info("查询sql明细分页======》"+sql);
	        lists= basedao.getListCglib(sql);
	        List<Billdetail> bilist=new ArrayList<Billdetail>();
	       for (CglibDynaBean cg : lists) {
            	Billdetail bi=new Billdetail();
            	bi.setGoodname((String) cg.getValue("goodname"));
            	bi.setGoodnumber((Double) cg.getValue("kccount"));
            	bi.setRkd((Double) cg.getValue("rkd"));
            	bi.setCkd((Double) cg.getValue("ckd"));
            	bilist.add(bi);
			}
	        PrintWriter pw = response.getWriter();
	        jsonMap.put("rows", bilist);
			//date类型的转化方式
		    Object jsonFromBean=GsonUtils.ConvertJsonDateToDate(jsonMap,"yyyy-MM-dd");
		    pw.write(jsonFromBean.toString());        
           	return null;
	}
	/**
	 * 
	 * @param request
	 * @return
	 * @throws IOException 
	 * @throws InvocationTargetException 
	 * @throws IllegalAccessException 
	 */
	@SuppressWarnings("rawtypes")
	@RequestMapping("/reporttubiaodate.html")
	public String getReportDate(HttpServletRequest request,HttpServletResponse response) throws IOException, IllegalAccessException, InvocationTargetException{

		String specifications=request.getParameter("specifications"); //规格型号
		String clientname=request.getParameter("clientname");//客户姓名
		String year=request.getParameter("year");//年

		 Map<String, Object> jsonMap = new HashMap<String, Object>();//定义map  
		 if(!"".equals(specifications)){
			 specifications = URLDecoder.decode(specifications, "utf-8");
	     } 
		 if(!"".equals(clientname)){
			 clientname = URLDecoder.decode(clientname, "utf-8");
	     } 
		BaseDaoInterface basedao=MysqlBaseDaoImpl.getInstance();
	        List<Billdetail> lists=null;
	        String sql="SELECT s2.month,SUM(s2.rkd) AS rkd,SUM(s2.ckd) AS ckd,(SUM(s2.rkd)-SUM(s2.ckd)) AS stockrate FROM (SELECT * FROM (SELECT DATE_FORMAT(createdate,'%Y%m') AS MONTH,SUM(goodnumber) AS rkd,0 AS ckd  FROM billdetail " +
	        		"WHERE DATE_FORMAT(createdate,'%Y')="+year+" AND billtype='入库单' AND states='已完成' AND specifications like '%"+specifications+"%'  AND clientname like '%"+clientname+"%' GROUP BY MONTH ORDER BY MONTH) s1 " +
	        		"UNION ALL SELECT * FROM (SELECT DATE_FORMAT(createdate,'%Y%m') AS MONTH,0 AS rkd,SUM(goodnumber) AS ckd FROM billdetail " +
	        		"WHERE DATE_FORMAT(createdate,'%Y')="+year+" AND billtype='出库单'  AND states='已完成' AND specifications like '%"+specifications+"%' AND clientname like '%"+clientname+"%' GROUP BY MONTH ORDER BY MONTH) s1) s2 GROUP BY s2.month";
	        logger.info("查询sql======》"+sql);
	        lists= basedao.getListBySql(sql, Billdetail.class, null);
	        List monthlist=new ArrayList();	
			Double [] stockrate=new Double[lists.size()];
	        String [] month=new String[lists.size()];
	        Double [] rkd=new Double[lists.size()];
	        Double [] ckd=new Double[lists.size()];
	       for (int i = 0; i < lists.size(); i++) {
	    	   Billdetail cg=lists.get(i);
	    	   stockrate[i]=cg.getStockrate();
	    	   rkd[i]= cg.getRkd();
	    	   ckd[i]=cg.getCkd();
	    	   month[i]= cg.getMonth();
		  }
	        PrintWriter pw = response.getWriter();
			//date类型的转化方式
		   // Object jsonFromBean=GsonUtils.ConvertJsonDateToDate(jsonMap,"yyyy-MM-dd");
		 //   pw.write(jsonFromBean.toString());    
		 //--------------------------------------图表生成----------------------------------------------------   
		      EnhancedOption option = new EnhancedOption();
		        option.title().text(year+"年出入库统计走势图").subtext("按月统计展示");
		        option.tooltip().trigger(Trigger.axis);
		        option.legend("入库量", "出库量", "库存量");
		        option.toolbox().show(true).feature(Tool.mark, Tool.dataView, new MagicType(Magic.line, Magic.bar).show(true), Tool.restore, Tool.saveAsImage);
		        option.calculable(true);
		        //option.xAxis(new CategoryAxis().data("1月", "2月", "3月", "4月", "5月", "6月", "7月", "8月", "9月", "10月", "11月", "12月"));
		        option.xAxis(new CategoryAxis().data(month));
		        option.yAxis(new ValueAxis());

		        Bar bar = new Bar("入库量");
		        bar.data(rkd);
		        bar.markPoint().data(new PointData().type(MarkType.max).name("最大值"), new PointData().type(MarkType.min).name("最小值"));
		        bar.markLine().data(new PointData().type(MarkType.average).name("平均值"));

		        Bar bar2 = new Bar("出库量");
		        bar2.data(ckd);
		        bar2.markPoint().data(new PointData().type(MarkType.max).name("最大值"), new PointData().type(MarkType.min).name("最小值"));
		        bar2.markLine().data(new PointData().type(MarkType.average).name("平均值"));
		        
		        Bar bar3 = new Bar("库存量");
		        List<Double> list = Arrays.asList(stockrate);
		        bar3.setData(list);
		        
		     //   bar3.data(stockratelist);
		        bar3.markPoint().data(new PointData().type(MarkType.max).name("最大值"), new PointData().type(MarkType.min).name("最小值"));
		        bar3.markLine().data(new PointData().type(MarkType.average).name("平均值"));
		        //将bar们放入option中
		        option.series(bar, bar2,bar3);
	        //转换为json
	        //System.out.println(option.toString());
	        //格式化的json，不利于传输，适合阅读
	        //System.out.println(option.toPrettyString());
	        pw.write(option.toString());    
           	return null;
	}
	
	
	@RequestMapping("/queryyears.html")  //获取明细数据中有的年份
	public String getBilldetailYears(HttpServletRequest request,HttpServletResponse response) throws IOException, IllegalAccessException, InvocationTargetException{
		BaseDaoInterface basedao=MysqlBaseDaoImpl.getInstance();
	        List<CglibDynaBean> lists=null;
	        String sql="SELECT DATE_FORMAT(createdate,'%Y') AS years FROM billdetail GROUP BY years ORDER BY years DESC";
	        logger.info("查询sql======》"+sql);
	        lists= basedao.getListCglib(sql);
	        List<Billdetail> bilist=new ArrayList<Billdetail>();
	       for (CglibDynaBean cg : lists) {
            	Billdetail bi=new Billdetail();
            	bi.setYears((String) cg.getValue("years"));
            	bilist.add(bi);
			}
	        PrintWriter pw = response.getWriter();
			JSONArray jsonObject = JSONArray.fromObject(bilist);
	        pw.write(jsonObject.toString());
           	return null;
	}	
}
